package com.bike.util;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;

import javax.el.ArrayELResolver;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import javassist.expr.NewArray;


public class ExcelUtil {
	 
	/**
	 * 根据excel里面的内容读取客户信息
	 * 
	 * @param is
	 *            输入流
	 * @param isExcel2003
	 *            excel是2003还是2007版本
	 * @return
	 * @throws IOException
	 */
	public static List<List<Object>> getExcelInfo(InputStream is,PageData pd,String filePath) {
		List<List<Object>> result = new ArrayList<>();
		try {
			boolean isExcel2003 = isExcel2003(filePath);
			Workbook wb = null;
			// 当excel是2003时
			if (isExcel2003) {
				wb = new HSSFWorkbook(is);
			} else {// 当excel是2007时
				wb = new XSSFWorkbook(is);
			}
			// 读取Excel里面客户的信息
			result= readExcelValue(wb, 0,pd);
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			if (is != null) {
				try {
					is.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
		}
		return result;
	}
		
		public static List<List<Object>> readExcelValue(Workbook wb,int i,Map<String, Object> param){
			//得到第一个shell
			Sheet sheet=wb.getSheetAt(i);
			//参加考试的所有班级
			Set<Object> classEs=new HashSet<>();
			//得到Excel的行数
			 int totalRows=sheet.getPhysicalNumberOfRows();
			//得到Excel的列数(前提是有行数)
			 int totalCells=0;
			if(totalRows>=1 && sheet.getRow(0) != null){
				totalCells=sheet.getRow(0).getPhysicalNumberOfCells();
			}
			List<List<Object>> infoList=new ArrayList<>();
			//循环Excel行数,从第二行开始。标题不入库
			for(int r=0;r<totalRows;r++){
				if(i==0&&r==0){//成绩 第一行 是标题
					continue;
				}
				Row row = sheet.getRow(r);
				if (row == null) continue;
				List<Object> item=new ArrayList<>();
				//循环Excel的列
				for(int c = 0; c <totalCells; c++){
						Cell cell = row.getCell(c);
					if (null != cell){
							Object kh=getCellValue(cell);
							item.add(kh);
					}
				}
				infoList.add(item);
			}
			return infoList;
		}
	
		
		
	// @描述：是否是2003的excel，返回true是2003 
	public static boolean isExcel2003(String filePath) { 
	return filePath.matches("^.+\\.(?i)(xls)$"); 
	}
	 
	//@描述：是否是2007的excel，返回true是2007
	public static boolean isExcel2007(String filePath) {
	 return filePath.matches("^.+\\.(?i)(xlsx)$");
	}
	
	public static Object getCellValue(Cell cell){
		Object value=null;
		try {
			switch (cell.getCellType()) {
			  case HSSFCell.CELL_TYPE_NUMERIC: // 数字
			      //如果为时间格式的内容
			      if (HSSFDateUtil.isCellDateFormatted(cell)) {      
			         //注：format格式 yyyy-MM-dd hh:mm:ss 中小时为12小时制，若要24小时制，则把小h变为H即可，yyyy-MM-dd HH:mm:ss
			         SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");  
			         value=sdf.format(HSSFDateUtil.getJavaDate(cell.
			         getNumericCellValue())).toString();                                 
			           break;
			       } else {
			           value =cell.getNumericCellValue();
			           if(String.valueOf(value).endsWith(".0")||String.valueOf(value).endsWith(".00")){
			        	   value= Integer.parseInt(value.toString().substring(0,value.toString().indexOf(".")));
			           }
			       }
			      break;
			  case HSSFCell.CELL_TYPE_STRING: // 字符串
			      value = cell.getStringCellValue().trim();
			      break;
			  case HSSFCell.CELL_TYPE_BOOLEAN: // Boolean
			      value = cell.getBooleanCellValue() + "";
			      break;
			  case HSSFCell.CELL_TYPE_FORMULA: // 公式
			      value = cell.getCellFormula() + "";
			      break;
			  case HSSFCell.CELL_TYPE_BLANK: // 空值
			      value = "0";
			      break;
			  case HSSFCell.CELL_TYPE_ERROR: // 故障
			      value = "非法字符";
			      break;
			  default:
			      value = "未知类型";
			      break;
 }
		} catch (Exception e) {
			value="0";
		}
		return value;
	}
	 
}
